using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using PropertyManagement.Core.Model;

namespace PropertyManagement.Core.DataAccess {
        public static partial class MaintainDataAccess {
             
            public static Maintain GetById(int id){
                var sql = @"select * from dbo.Maintain where MaintainID = @id ";
                var paramList = new[] {
                    new SqlParameter("id", id)
                };
                using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationMaintainByIReader(iReader);
                    };
                    return null;
                }
            }

            public static void Add(Maintain entity){
                  var sql = @"insert into dbo.Maintain values (@houseid,@maintaincode,@status,@maintainfee,@maintaintype,@maintaintopic,@remark,@applydate,@repairedate,@repaireworker,@creationdate,@lastupdate,@creationuserid,@lastactionuserid) ;   select SCOPE_IDENTITY (); ";
                  var paramList = new SqlParameter[] {
							new SqlParameter("applydate",entity.ApplyDate),
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("houseid",entity.HouseID),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastupdate",entity.LastUpdate),
							new SqlParameter("maintaincode",entity.MaintainCode),
							new SqlParameter("maintainfee",entity.MaintainFee),
							new SqlParameter("maintaintopic",entity.MaintainTopic),
							new SqlParameter("maintaintype",entity.MaintainType),
							new SqlParameter("remark",entity.Remark),
							new SqlParameter("repairedate",entity.RepaireDate),
							new SqlParameter("repaireworker",entity.RepaireWorker),
							new SqlParameter("status",entity.Status)
                        };
                  using ( var ireader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                            paramList)) {
                        if (ireader.Read()) {
                            entity.MaintainID = int.Parse(ireader[0].ToString());
                        }
                  }
            
            }   

            public static void Remove(Maintain entity) {
                var sql = @"delete from dbo.Maintain where MaintainID = @id";
                var paramList = new[] {
                    new SqlParameter("id", entity.MaintainID)
                };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }   

            public static IEnumerable<Maintain> LoadAll(){
                    var sql = " select * from dbo.Maintain ";
                    var list = new List<Maintain>( );
                    using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql)) {
                        while (iReader.Read( )) {
                                list.Add(CreationMaintainByIReader(iReader));
                        };
                    }  
                    return list;
            }   
        
            public static void Update(Maintain entity){
                var sql = @" update dbo.Maintain set ApplyDate=@applydate,CreationDate=@creationdate,CreationUserID=@creationuserid,HouseID=@houseid,LastActionUserID=@lastactionuserid,LastUpdate=@lastupdate,MaintainCode=@maintaincode,MaintainFee=@maintainfee,MaintainTopic=@maintaintopic,MaintainType=@maintaintype,Remark=@remark,RepaireDate=@repairedate,RepaireWorker=@repaireworker,Status=@status where MaintainID = @id ";
                var paramList = new SqlParameter[] {
                            new SqlParameter("id",entity.MaintainID),
							new SqlParameter("applydate",entity.ApplyDate),
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("houseid",entity.HouseID),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastupdate",entity.LastUpdate),
							new SqlParameter("maintaincode",entity.MaintainCode),
							new SqlParameter("maintainfee",entity.MaintainFee),
							new SqlParameter("maintaintopic",entity.MaintainTopic),
							new SqlParameter("maintaintype",entity.MaintainType),
							new SqlParameter("remark",entity.Remark),
							new SqlParameter("repairedate",entity.RepaireDate),
							new SqlParameter("repaireworker",entity.RepaireWorker),
							new SqlParameter("status",entity.Status)
                        };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }
            
            public static bool Exists(Maintain entity){
                return entity.MaintainID != 0 && GetById(entity.MaintainID)!=null;
            }


            private static Maintain CreationMaintainByIReader(IDataReader iReader){
                if(iReader!=null){
                      return new Maintain{
							ApplyDate = iReader["ApplyDate"] is DBNull ? default(DateTime) : (DateTime)iReader["ApplyDate"] ,
							CreationDate = iReader["CreationDate"] is DBNull ? default(DateTime) : (DateTime)iReader["CreationDate"] ,
							CreationUserID = iReader["CreationUserID"] is DBNull ? default(Int32) : (Int32)iReader["CreationUserID"] ,
							HouseID = iReader["HouseID"] is DBNull ? default(Int32) : (Int32)iReader["HouseID"] ,
							LastActionUserID = iReader["LastActionUserID"] is DBNull ? default(Int32) : (Int32)iReader["LastActionUserID"] ,
							LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"] ,
							MaintainCode = iReader["MaintainCode"] is DBNull ? default(String) : (String)iReader["MaintainCode"] ,
							MaintainFee = iReader["MaintainFee"] is DBNull ? default(Decimal) : (Decimal)iReader["MaintainFee"] ,
							MaintainID = iReader["MaintainID"] is DBNull ? default(Int32) : (Int32)iReader["MaintainID"] ,
							MaintainTopic = iReader["MaintainTopic"] is DBNull ? default(String) : (String)iReader["MaintainTopic"] ,
							MaintainType = iReader["MaintainType"] is DBNull ? default(MaintainType) : (MaintainType)iReader["MaintainType"] ,
							Remark = iReader["Remark"] is DBNull ? default(String) : (String)iReader["Remark"] ,
							RepaireDate = iReader["RepaireDate"] is DBNull ? default(DateTime) : (DateTime)iReader["RepaireDate"] ,
							RepaireWorker = iReader["RepaireWorker"] is DBNull ? default(String) : (String)iReader["RepaireWorker"] ,
							Status = iReader["Status"] is DBNull ? default(Boolean) : (Boolean)iReader["Status"] 
                        };
                }
                return null; 
            }

            
            
            public static Maintain GetByPKs(Maintain entity){
                var sql =  @"select *  from dbo.Maintain where MaintainCode = @maintaincode ";
                var paramList = new SqlParameter[] {
							new SqlParameter("maintaincode",entity.MaintainCode)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationMaintainByIReader(iReader);
                    };
                    return null;
                }
            }

            public static bool ExistsByPKs(Maintain entity){
                var sql =  @"select *  from dbo.Maintain where MaintainCode = @maintaincode ";
                var paramList = new SqlParameter[] {
							new SqlParameter("maintaincode",entity.MaintainCode)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    return iReader.Read( );
                }
            }

    }
}